package com.bank.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bank.utils.DbUtils;

/**
 */
public class AccountDAO {
	//元素的下标【1】是身份证  【8】是密码
	public static final Object[] columnNames = { "账户", "身份证", "姓名", "手机号", "余额", "开户时间", "类型", "状态", "密码" };

	public static Object[][] toList(List<Object[]> list) {
		Object[][] result = new Object[list.size()][];
		for (int i = 0; i < list.size(); i++) {
			result[i] = list.get(i);
		}
		return result;
	}

	/**
	 * 单条件查询
     */
	public static Object[][] search(int col, String text) {
		String sql = "select card,id,real_name,mobile,balance,created_time,type,status,password from bank_account ";

		List<Object[]> result = new ArrayList<>();
		List<Object> param = new ArrayList<>();
		if (col == 1) {
			if (text != null && !text.trim().isEmpty()) {
				sql += " where id=?";
				param.add(text);
			}
		}
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DbUtils.open();
			ps = DbUtils.preparedStatement(sql, param, connection);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] data = new Object[columnNames.length];
				data[0] = rs.getString("card");
				data[1] = rs.getString("id");
				data[2] = rs.getString("real_name");
				data[3] = rs.getString("mobile");
				data[4] = rs.getDouble("balance");
				data[5] = rs.getString("created_time");
				data[6] = rs.getString("type");
				data[7] = rs.getString("status");
				data[8] = rs.getString("password");
				result.add(data);
			}
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			DbUtils.closeAll(connection, ps, null);
		}
		return toList(result);
	}

	/**
	 * 查询用户名下有几张卡
	 *            I类或者Ⅱ类，null表示所有类型
     */
	public static int getUserAccountCount(String id, String type) {
		String sql = "select count(*) from bank_account where id=? and status='正常'";

		List<Object> param = new ArrayList<>();
		param.add(id);
		if (type != null && !type.trim().isEmpty()) {
			sql += " and type=?";
			param.add(type);
		}
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DbUtils.open();
			ps = DbUtils.preparedStatement(sql, param, connection);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeAll(connection, ps, null);
		}
		return 0;
	}

	/**
	 * 根据卡号查询
     */
	public static Object[] findByCard(String card) {
		String sql = "select card,id,real_name,mobile,balance,created_time,type,status,password from bank_account where card=?";

		List param = new ArrayList<>();
		param.add(card);
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DbUtils.open();
			ps = DbUtils.preparedStatement(sql, param, connection);
			rs = ps.executeQuery();
			if (rs.next()) {
				Object[] data = new Object[columnNames.length];
				data[0] = rs.getString("card");
				data[1] = rs.getString("id");
				data[2] = rs.getString("real_name");
				data[3] = rs.getString("mobile");
				data[4] = rs.getDouble("balance");
				data[5] = rs.getString("created_time");
				data[6] = rs.getString("type");
				data[7] = rs.getString("status");
				data[8] = rs.getString("password");
				return data;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeAll(connection, ps, null);
		}
		return null;
	}

	/**
	 * 添加
     */
	public static void add(Object[] obj) {
		String sql = "insert into bank_account(card,id,real_name,mobile,balance,created_time,type,status,password) values(?,?,?,?,?,?,?,?,?)";
		List param = new ArrayList<>();
		for (Object o : obj) {
			param.add(o);
		}
		DbUtils.executeUpdate(sql, param);
	}

	/**
	 * 修改(管理员或者余额用到)，但不修改支付密码
     */
	public static void update(String card, Object[] obj) {
		String sql = "update bank_account set mobile=?,balance=?,type=?,status=? where card=?";
		List param = new ArrayList<>();
		param.add(obj[3]);
		param.add(obj[4]);
		param.add(obj[6]);
		param.add(obj[7]);
		param.add(card);
		DbUtils.executeUpdate(sql, param);
	}

	/**
	 * 修改密码
     */
	public static void updatePassword(String card, String password) {
		String sql = "update bank_account set password=? where card=?";
		List param = new ArrayList<>();
		param.add(password);
		param.add(card);
		DbUtils.executeUpdate(sql, param);
	}

}
